﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for UserDAL
/// </summary>
public class UserDAL
{
    DataAccess db;
    public UserDAL()
	{
        db = new DataAccess();
    }
    public int Insert_UpdateUser(User user) 
    {
        SqlParameter[] paraList = new SqlParameter[10];
        paraList[0] = db.AddParameter("user_id", user.User_id, SqlDbType.Int, null);
        paraList[1] = db.AddParameter("user_name", user.User_name, SqlDbType.NVarChar, 50);
        paraList[2] = db.AddParameter("password", user.Password, SqlDbType.NVarChar, 50);
        paraList[3] = db.AddParameter("full_name", user.FullName, SqlDbType.NVarChar, 50);
        paraList[4] = db.AddParameter("birthday", user.Birthday, SqlDbType.DateTime, null);
        paraList[5] = db.AddParameter("email",user.Email,SqlDbType.NVarChar,50);
        paraList[6] = db.AddParameter("sex", user.Sex, SqlDbType.Bit, null);
        paraList[7] = db.AddParameter("address", user.Address, SqlDbType.NVarChar, 50);
        paraList[8] = db.AddParameter("phone", user.Phone, SqlDbType.NVarChar, 50);
        paraList[9] = db.AddParameter("user_group_id", user.User_group_id, SqlDbType.Int, null);
        return db.executeUpdate("IS_User", paraList);
 
    }
    public DataTable Login(string username, string pass)
    {
        SqlParameter[] paraList = new SqlParameter[2];
        paraList[0] = db.AddParameter("user_name", username, SqlDbType.NVarChar, 50);
        paraList[1] = db.AddParameter("password", pass, SqlDbType.VarChar, 50);
        return db.executeSelect("Login", paraList);
    }
    public DataTable getUserByUserName(string user_name) 
    {
        SqlParameter[] paraList = new SqlParameter[3];
        paraList[0] = db.AddParameter("user_id",null , SqlDbType.Int , null);
        paraList[1] = db.AddParameter("user_name", user_name, SqlDbType.NVarChar, 50);
        paraList[2] = db.AddParameter("email", null, SqlDbType.NVarChar, 50);
        return db.executeSelect("SL_User", paraList);
    }

    public DataTable getUserByEmail(string email) 
    {
        SqlParameter[] paraList = new SqlParameter[3];
        paraList[0] = db.AddParameter("user_id", null, SqlDbType.Int, null);
        paraList[1] = db.AddParameter("user_name", null, SqlDbType.NVarChar, 50);
        paraList[2] = db.AddParameter("email", email, SqlDbType.NVarChar, 50);
        return db.executeSelect("SL_User", paraList);
    }

    public DataTable getUserByID(int user_id)
    {
        SqlParameter[] paraList = new SqlParameter[3];
        paraList[0] = db.AddParameter("user_id", user_id, SqlDbType.Int, null);
        paraList[1] = db.AddParameter("user_name", DBNull.Value, SqlDbType.NVarChar, 50);
        paraList[2] = db.AddParameter("email", DBNull.Value, SqlDbType.NVarChar, 50);
        return db.executeSelect("SL_User", paraList);
    }

    public DataTable getAllUsers()
    {
        SqlParameter[] paraList = new SqlParameter[3];
        paraList[0] = db.AddParameter("user_id", DBNull.Value, SqlDbType.Int, null);
        paraList[1] = db.AddParameter("user_name", DBNull.Value, SqlDbType.NVarChar, 50);
        paraList[2] = db.AddParameter("email", DBNull.Value, SqlDbType.NVarChar, 50);
        return db.executeSelect("SL_User", paraList);
    }

    public int DeleteUserByID(int user_id)
    {
        SqlParameter[] paraList = new SqlParameter[1];
        paraList[0] = db.AddParameter("user_id", user_id, SqlDbType.Int, null);

        return db.executeUpdate("DELETE_USER", paraList);
    }
}